﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Reflection;
using System.Collections;
using System.IO.Ports;
using NLog;
using NPOI.SS.Util;
using System.Linq;

namespace MainAppEntryPrismWpf.Utilities
{

    public static class TableListConverter
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> varlist)
        {
            DataTable dtReturn = new DataTable();
            // column names
            PropertyInfo[] oProps = null;
            // Could add a check to verify that there is an element 0
            foreach (T rec in varlist)
            {
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();

                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }
                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }

                DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
                }
                dtReturn.Rows.Add(dr);
            }
            return (dtReturn);
        }
        public delegate object[] CreateRowDelegate<T>(T t);

        public static DataTable ListToTable<T>(IList<T> list) where T : class, new()
        {
            if (list == null) return null;
            Type type = typeof(T);
            DataTable dt = new DataTable();

            PropertyInfo[] properties = Array.FindAll(type.GetProperties(), p => p.CanRead);//判断此属性是否有Getter
            Array.ForEach(properties, prop => { dt.Columns.Add(prop.Name, prop.PropertyType); });//添加到列
            foreach (T t in list)
            {
                DataRow row = dt.NewRow();
                Array.ForEach(properties, prop =>
                {
                    row[prop.Name] = prop.GetValue(t, null);
                });//添加到行
                dt.Rows.Add(row);
            }
            return dt;
        }
    }
    public class ExcelHelperOld : IDisposable
    {
        private string fileName = null; //文件名
        private IWorkbook workbook = null;
        private FileStream fs = null;
        private bool disposed;

        public ExcelHelperOld(string fileName)
        {
            this.fileName = fileName;
            disposed = false;
        }

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;


            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();


            // 样式1：红色底纹的边框
            ICellStyle redCellStyle = workbook.CreateCellStyle();
            redCellStyle.BorderBottom = BorderStyle.Thin;
            redCellStyle.BorderTop = BorderStyle.Thin;
            redCellStyle.BorderLeft = BorderStyle.Thin;
            redCellStyle.BorderRight = BorderStyle.Thin;
            redCellStyle.FillForegroundColor = IndexedColors.Red.Index;
            redCellStyle.FillPattern = FillPattern.SolidForeground;

            // 样式2：红色边框
            ICellStyle redBorderCellStyle = workbook.CreateCellStyle();
            redBorderCellStyle.BorderBottom = BorderStyle.Thin;
            redBorderCellStyle.BorderTop = BorderStyle.Thin;
            redBorderCellStyle.BorderLeft = BorderStyle.Thin;
            redBorderCellStyle.BorderRight = BorderStyle.Thin;
            redBorderCellStyle.RightBorderColor = IndexedColors.Red.Index;

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                if (isColumnWritten)
                {
                    IRow headerRow = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; j++)
                    {
                        ICell cell = headerRow.CreateCell(j);
                        cell.SetCellValue(data.Columns[j].ColumnName);
                        // Add border to header cells
                      
                        ICellStyle style = workbook.CreateCellStyle();
                        style.BorderBottom = BorderStyle.Thin;
                        style.BorderLeft = BorderStyle.Thin;
                        style.BorderRight = BorderStyle.Thin;
                        style.BorderTop = BorderStyle.Thin;
                        cell.CellStyle = redCellStyle;

                        // sheet.AutoSizeColumn(j);
                        sheet.SetColumnWidth(j, 5000);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellValue(data.Rows[i][j].ToString());
                        // Add border to data cells
                        ICellStyle style = workbook.CreateCellStyle();
                        style.BorderBottom = BorderStyle.Thin;
                        style.BorderLeft = BorderStyle.Thin;
                        style.BorderRight = BorderStyle.Thin;
                        style.BorderTop = BorderStyle.Thin;
                        if (i/2 ==0)
                        {
                            cell.CellStyle = style;
                        }
                        else
                        {
                            cell.CellStyle = redBorderCellStyle;
                        }
                        
                    }
                    count++;
                }

                // Auto-size columns
                for (j = 0; j < data.Columns.Count; j++)
                {
                   // sheet.AutoSizeColumn(j);
                    sheet.SetColumnWidth(j, 5000);
                }

                workbook.Write(fs); // 写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }


        #region 将DataTable数据导出到Excel文件中

        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xls)
        /// </summary>
        /// <param name="_dt">数据源</param>
        /// <param name="file">模板路径</param>
        /// <param name="AL_Name">每个Sheet名字</param>
        public void TableToExcelForXLS(DataTable[] _dt, string file, ArrayList AL_Name)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            for (int k = 0; k < _dt.Length; k++)
            {
                ISheet sheet = hssfworkbook.CreateSheet(AL_Name[k].ToString());

                //表头
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < _dt[k].Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(_dt[k].Columns[i].ColumnName);
                }

                //数据
                for (int i = 0; i < _dt[k].Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < _dt[k].Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(_dt[k].Rows[i][j].ToString());
                    }
                }
            }
            //转为字节数组
            MemoryStream stream = new MemoryStream();
            hssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }

        }
        #endregion

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (fs != null)
                        fs.Close();
                }

                fs = null;
                disposed = true;
            }
        }
    }

    public class Global
    {
        public static Logger logger = null;
        public static SerialPort ComShaker = null;
    }

    public class ExcelHelper
    {
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath">文件目录</param>
        /// <param name="fileName">文件名，为空时默认：新建Excel.xlsx</param>
        /// <param name="headers">表格头</param>
        /// <param name="data">表格数据</param>
        /// <returns></returns>
        public static string ExportExcel<T>(string filePath, string fileName, IEnumerable<ExcelHeader> headers, ExcelData<T> data)
        {
            //导出的excel文件地址
            string excelPath = string.Empty;
            #region 文件名称处理
            //为空时设置默认文件名
            if (string.IsNullOrWhiteSpace(fileName))
            {
                fileName = "新建Excel.xlsx";
            }
            //目录为空时取项目基目录
            if (string.IsNullOrWhiteSpace(filePath))
            {
                filePath = AppContext.BaseDirectory;
            }
            if (!Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }
            if (!filePath.EndsWith("\\") && !filePath.EndsWith("/"))
            {
                filePath = filePath + "\\";
            }
            excelPath = $"{filePath}{fileName}";
            #endregion

            //初始化
            // IWorkbook workbook = new HSSFWorkbook();  //XSSFWorkbook
            IWorkbook workbook = new XSSFWorkbook();
            //工作簿
            ISheet sheetTable = workbook.CreateSheet();
            //生成表头
            #region 表头
            foreach (var item in headers)
            {
                IRow headerRow = sheetTable.GetRow(item.FirstRow);
                if (headerRow == null)
                {
                    headerRow = sheetTable.CreateRow(item.FirstRow);
                    //行高，避免自动换行的内容将行高撑开
                    headerRow.HeightInPoints = 24;
                }
                ICell headerCell = headerRow.CreateCell(item.FirstCol);
                headerCell.SetCellValue(item.Value);
                //设置跨行
                if (item.FirstRow != item.LastRow || item.LastCol != item.FirstCol)
                {
                    //CellRangeAddress(开始行,结束行,开始列,结束列)
                    //行列索引由0开始
                    var region = new CellRangeAddress(item.FirstRow, item.LastRow, item.FirstCol, item.LastCol);
                    sheetTable.AddMergedRegion(region);
                }
                headerCell.CellStyle = HeaderStyle(workbook);
            }
            #endregion

            #region 表格数据
            var type = data.Data.First()?.GetType();//获取列表的字段的属性
            var properties = type.GetProperties();//筛选出需要
            //加载数据
            foreach (var item in data.Data)
            {
                IRow dataRow = sheetTable.GetRow(data.StartRow);

               
                if (dataRow == null)
                {
                    dataRow = sheetTable.CreateRow(data.StartRow);
                    //行高，避免自动换行的内容将行高撑开
                    dataRow.HeightInPoints = 20;
                }
                var startCol = data.StartCol;
                sheetTable.SetColumnWidth(startCol,5000);
                foreach (var item1 in properties)
                {
                    ICell dataCell = dataRow.CreateCell(startCol);
                    var dataValue = item1.GetValue(item);
                    dataCell.CellStyle = DataDefautStyle(workbook);
                    IDataFormat dataformat = workbook.CreateDataFormat();
                    //设置内容格式
                    if (dataValue?.GetType() == typeof(int)
                        || dataValue?.GetType() == typeof(decimal)
                        || dataValue?.GetType() == typeof(double)
                        || dataValue?.GetType() == typeof(float))
                    {
                        double dataValueD = 0;
                        double.TryParse(dataValue?.ToString(), out dataValueD);
                        dataCell.SetCellValue(dataValueD);
                      
                        //精确到小数点后两位
                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("0.00");
                        //添加千分位分割,并保留两位小数
                        dataCell.CellStyle.DataFormat = dataformat.GetFormat("#,##0.00");
                        //金钱格式-千分位分割,并保留两位小数
                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("￥#,##0.00");
                        //中文大写(会有四舍五入的情况)
                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("[DbNum2][$-804]0");
                        //科学计数法
                        //dataCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    }
                    else if (dataValue?.GetType() == typeof(DateTime))
                    {
                        DateTime.TryParse(dataValue?.ToString(), out DateTime date);
                        dataCell.SetCellValue(date);
                        dataCell.CellStyle.DataFormat = dataformat.GetFormat("yyyy-MM-dd hh:mm:ss");
                    }
                    else
                    {
                        dataCell.SetCellValue(dataValue?.ToString());
                        dataCell.CellStyle.DataFormat = dataformat.GetFormat("text");
                    }
                    startCol++;
                    sheetTable.SetColumnWidth(startCol, 5000);
                }
                startCol = data.StartCol;
                data.StartRow++;
            }
            #endregion

            #region 生成文件
            FileStream fs = File.Open(excelPath, FileMode.Create, FileAccess.Write);
            workbook.Write(fs);
            sheetTable = null;
            workbook = null;
            fs.Close();
            fs.Dispose();
            #endregion

            return excelPath;
        }
        /// <summary>
        /// 数据单元格样式
        /// </summary>
        private static ICellStyle DataDefautStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center; //居中
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
            style.WrapText = true;//自动换行
           // 边框
           //style.BorderBottom = BorderStyle.Thin;
           //style.BorderLeft = BorderStyle.Thin;
           //style.BorderRight = BorderStyle.Thin;
           //style.BorderTop = BorderStyle.Thin;
           // 字体
           IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 12;
            font.FontName = "宋体";
            style.SetFont(font);

            return style;
        }
        /// <summary>
        /// 表头样式
        /// </summary>
        public static ICellStyle HeaderStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            //居中
            style.Alignment = HorizontalAlignment.Center;
            //垂直居中
            style.VerticalAlignment = VerticalAlignment.Center;
            //自动换行 
            style.WrapText = true;
            //边框
            //style.BorderBottom = BorderStyle.Thin;
            //style.BorderLeft = BorderStyle.Thin;
            //style.BorderRight = BorderStyle.Thin;
            //style.BorderTop = BorderStyle.Thin;
            //边框颜色
            //style.TopBorderColor = HSSFColor.Black.Index;
            //style.BottomBorderColor = HSSFColor.Black.Index;
            //style.RightBorderColor = HSSFColor.Black.Index;
            //style.LeftBorderColor = HSSFColor.Black.Index;
            //字体
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 14;
            font.FontName = "宋体";
            font.IsBold = true;
            style.SetFont(font);
            return style;
        }
    }
    /// <summary>
    /// 表头格式
    /// </summary>
    public class ExcelHeader
    {
        /// <summary>
        /// 标题
        /// </summary>
        public string Value { get; set; }
        /// <summary>
        /// 开始行，索引0开始
        /// </summary>
        public int FirstRow { get; set; }
        /// <summary>
        /// 结束行，索引0开始
        /// </summary>
        public int LastRow { get; set; }
        /// <summary>
        /// 开始列，索引0开始
        /// </summary>
        public int FirstCol { get; set; }
        /// <summary>
        /// 结束列，索引0开始
        /// </summary>
        public int LastCol { get; set; }
    }
    /// <summary>
    /// 表数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class ExcelData<T>
    {
        /// <summary>
        /// 表数据起始行，索引0开始 
        /// </summary>
        public int StartRow { get; set; }
        /// <summary>
        /// 表数据起始列，索引0开始
        /// </summary>
        public int StartCol { get; set; }
        /// <summary>
        /// 行数据
        /// </summary>
        public IEnumerable<T> Data { get; set; }
    }




}
